/*
 *此javaCode由YMS-JDBC替换工具生成，原mapper位置com.yonyou.ucf.mdf.app.isvdxq.firstpart.stock.mapper.StockMapper
 */
package com.yonyou.ucf.mdf.app.isvdxq.firstpart.stock.mapper;

import com.yonyou.iuap.yms.api.IYmsJdbcApi;
import com.yonyou.iuap.yms.param.*;
import com.yonyou.iuap.yms.param.ResultSetProcessor;
import com.yonyou.iuap.yms.processor.*;
import com.yonyou.iuap.yms.replace.CollectionUtil;
import com.yonyou.ucf.mdf.app.isvdxq.common.DxqConfig;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.RFIDCodeDetail.entity.storeRecord;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.pandian.model.CheckProductDto;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.stock.model.ProductInfoBean;
import com.yonyou.ucf.mdf.app.isvdxq.firstpart.stock.model.productInfo;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class StockMapperImpl {
    
    @Autowired
    private IYmsJdbcApi ymsJdbcApi;

    public List<CheckProductDto> GetGoodsByIds(List<String> Ids, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select c.id as id, ");
		sqlBuilder.append(" c.product_id as product_id, ");
		sqlBuilder.append(" p.productName as product_name, ");
		sqlBuilder.append(" p.productClass as productClass, ");
		sqlBuilder.append(" p.code as product_code, ");
		sqlBuilder.append(" c.product_sku_id as product_sku_id, ");
		sqlBuilder.append(" c.product_sku_name as product_sku_name, ");
		sqlBuilder.append(" u.unitName as unit_name, ");
		sqlBuilder.append(" c.f_stock_count as f_stock_count, ");
		sqlBuilder.append(" c.f_move_count as f_move_count, ");
		sqlBuilder.append(" c.f_out_count as f_out_count, ");
		sqlBuilder.append(" ttlyu.locationName as locationName, ");
		sqlBuilder.append(" ttlyu.id as location_id, ");
		sqlBuilder.append(" ttlyu.locationCode as locationCode, ");
		sqlBuilder.append(" c.rfid_code as rfid_code, ");
		sqlBuilder.append(" c.batchno as batchno, ");
		sqlBuilder.append(" c.rfid_status as rfid_status, ");
		sqlBuilder.append(" c.invalidDate as invalidDate, ");
		sqlBuilder.append(" c.productDate as productDate ");
		sqlBuilder.append(" from dxq_coderfid c ");
		sqlBuilder.append(" left join dxq_location ttlyu on c.location_id = ttlyu.id ");
		sqlBuilder.append(" left join dxqyy_product p on p.id = c.product_id ");
		sqlBuilder.append(" left join dxqyy_unit u on p.unit = u.id ");
		sqlBuilder.append(" where c.dr = 0 ");
		if (CollectionUtil.isNotEmpty(Ids)) {
			sqlBuilder.append(" and c.location_id in ( ");
			String separator0 = " , ";
			for (int index = 0; index < Ids.size(); index++) {
				String id = Ids.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(id);
				if (index < Ids.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		sqlBuilder.append(" and c.ytenant_id=? ");
		parameter.addParam(tenant_id);
		sqlBuilder.append(" and c.rfid_status IN(" + DxqConfig.PdStatic +"); ");
		//sqlBuilder.append(" and c.rfid_status IN(1,2,3); ");

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new CheckProductDto());
        return (List<CheckProductDto>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public List<ProductInfoBean> selectByRfidCode(List<String> strList, List<String> warehouseIds, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select cr.id, ");
		sqlBuilder.append(" cr.product_id, ");
		sqlBuilder.append(" p.productName as product_name, ");
		sqlBuilder.append(" cr.product_sku_id, ");
		sqlBuilder.append(" pst.skuName as product_sku_name, ");
		sqlBuilder.append(" u.unitName as unit_name, ");
		sqlBuilder.append(" u2.unitName as stock_unit_name, ");
		sqlBuilder.append(" cr.location_id, ");
		sqlBuilder.append(" ttly7u.rfid_code as locationRfidCode, ");
		sqlBuilder.append(" ttly7u.locationCode as locationCode, ");
		sqlBuilder.append(" ttly7u.locationName as locationName, ");
		sqlBuilder.append(" pst.code as product_sku_code, ");
		sqlBuilder.append(" p.code as product_code, ");
		sqlBuilder.append(" cr.warehouse_id, ");
		sqlBuilder.append(" w.warehouseName as warehouse_name, ");
		sqlBuilder.append(" w.code as warehouse_code, ");
		sqlBuilder.append(" cr.rfid_code, ");
		sqlBuilder.append(" cr.is_split, ");
		sqlBuilder.append(" cr.f_stock_count, ");
		sqlBuilder.append(" cr.f_move_count, ");
		sqlBuilder.append(" cr.f_stock_count, ");
		sqlBuilder.append(" cr.modify_time, ");
		sqlBuilder.append(" cr.rfid_status, ");
		sqlBuilder.append(" cr.batchno, ");
		sqlBuilder.append(" cr.batch_no, ");
		sqlBuilder.append(" cr.remark, ");
		sqlBuilder.append(" cr.productDate as productDate, ");
		sqlBuilder.append(" cr.invalidDate as invalidDate ");
		sqlBuilder.append(" from dxq_coderfid as cr ");
		sqlBuilder.append(" left join dxqyy_product p on p.id = cr.product_id ");
		sqlBuilder.append(" left join dxq_location ttly7u on cr.location_id = ttly7u.id ");
		sqlBuilder.append(" left join dxqyy_productskutemp pst on cr.product_sku_id = pst.id ");
		sqlBuilder.append(" left join dxqyy_unit u on p.unit = u.id ");
		sqlBuilder.append(" left join dxqyy_unit u2 on p.stockUnit = u2.id ");
		sqlBuilder.append(" left join dxqyy_warehouse w on w.id = cr.warehouse_id ");
		sqlBuilder.append(" where cr.dr = 0 ");
		if (CollectionUtil.isNotEmpty(strList)) {
			sqlBuilder.append(" and cr.rfid_code in ( ");
			String separator0 = " , ";
			for (int index = 0; index < strList.size(); index++) {
				String rfidCode = strList.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(rfidCode);
				if (index < strList.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
        if (CollectionUtil.isNotEmpty(warehouseIds)) {
            sqlBuilder.append(" and cr.warehouse_id in ");
            sqlBuilder.append(" ( ");
            String separator1 = " , ";
            for (int index = 0; index < warehouseIds.size(); index++) {
                String item = warehouseIds.get(index);
                sqlBuilder.append(" ? ");
                parameter.addParam(item);
                if (index < warehouseIds.size() - 1) {
                    sqlBuilder.append(separator1);
                }
            }
            sqlBuilder.append(" ) ");
        }

		sqlBuilder.append(" and cr.ytenant_id = ? ");
		parameter.addParam(tenant_id);
		sqlBuilder.append(" order by cr.create_time DESC ");

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new ProductInfoBean());
        return (List<ProductInfoBean>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public List<productInfo> selectByRfidCodeAndPageHuoWei(List<String> rfidCode, String productName, int page, int pageSize, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select cr.id, ");
		sqlBuilder.append(" cr.product_id, ");
		sqlBuilder.append(" p.productName as product_name, ");
		sqlBuilder.append(" cr.product_sku_id, ");
		sqlBuilder.append(" pst.skuName as product_sku_name, ");
		sqlBuilder.append(" u.unitName as unit_name, ");
		sqlBuilder.append(" u2.unitName as stock_unit_name, ");
		sqlBuilder.append(" cr.location_id, ");
		sqlBuilder.append(" ttly7u.rfid_code as locationRfidCode, ");
		sqlBuilder.append(" ttly7u.locationCode as locationCode, ");
		sqlBuilder.append(" ttly7u.locationName as locationName, ");
		sqlBuilder.append(" pst.code as product_sku_code, ");
		sqlBuilder.append(" p.code as product_code, ");
		sqlBuilder.append(" cr.warehouse_id, ");
		sqlBuilder.append(" cr.rfid_code, ");
		sqlBuilder.append(" cr.f_stock_count, ");
		sqlBuilder.append(" cr.f_move_count, ");
		sqlBuilder.append(" cr.modify_time, ");
		sqlBuilder.append(" cr.rfid_status, ");
		sqlBuilder.append(" cr.batchno, ");
		sqlBuilder.append(" cr.batch_no, ");
		sqlBuilder.append(" cr.remark, ");
		sqlBuilder.append(" cr.invalidDate as invalidDate, ");
		sqlBuilder.append(" cr.productDate as productDate ");
		sqlBuilder.append(" from dxq_location as dl ");
		sqlBuilder.append(" inner join dxq_coderfid as cr on cr.location_id = dl.id ");
		sqlBuilder.append(" left join dxqyy_product p on p.id = cr.product_id ");
		sqlBuilder.append(" left join dxq_location ttly7u on cr.location_id = ttly7u.id ");
		sqlBuilder.append(" left join dxqyy_productskutemp pst on cr.product_sku_id = pst.id ");
		sqlBuilder.append(" left join dxqyy_unit u on p.unit = u.id ");
		sqlBuilder.append(" left join dxqyy_unit u2 on p.stockUnit = u2.id ");
		sqlBuilder.append(" where 1=1 ");
		if (CollectionUtil.isNotEmpty(rfidCode)) {
			sqlBuilder.append(" and dl.rfid_code in ");
			sqlBuilder.append(" ( ");
			String separator0 = " , ";
			for (int index = 0; index < rfidCode.size(); index++) {
				String rfid = rfidCode.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(rfid);
				if (index < rfidCode.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		if (productName != null && !productName.equals("")) {
			sqlBuilder.append(" and p.productName like concat('%',?,'%') ");
			parameter.addParam(productName);
		}
		sqlBuilder.append(" and cr.ytenant_id=? ");
		parameter.addParam(tenant_id);
		sqlBuilder.append(" and dl.dr = 0 ");
		sqlBuilder.append(" and cr.dr = 0 ");
		sqlBuilder.append(" group by cr.id ");
		sqlBuilder.append(" order by cr.id ");
		sqlBuilder.append(" limit ? ,? ");
		parameter.addParam(page);
		parameter.addParam(pageSize);

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new productInfo());
        return (List<productInfo>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public List<productInfo> selectByRfidCodeAndPage(List<String> warehouseIds, String rfidCode, String productName, String tenant_id, int page, int pageSize) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select cr.id, ");
		sqlBuilder.append(" cr.product_id, ");
		sqlBuilder.append(" p.productName as product_name, ");
		sqlBuilder.append(" cr.product_sku_id, ");
		sqlBuilder.append(" pst.skuName as product_sku_name, ");
		sqlBuilder.append(" u.unitName as unit_name, ");
		sqlBuilder.append(" u2.unitName as stock_unit_name, ");
		sqlBuilder.append(" cr.location_id, ");
		sqlBuilder.append(" ttly7u.rfid_code as locationRfidCode, ");
		sqlBuilder.append(" ttly7u.locationCode as locationCode, ");
		sqlBuilder.append(" ttly7u.locationName as locationName, ");
		sqlBuilder.append(" pst.code as product_sku_code, ");
		sqlBuilder.append(" p.code as product_code, ");
		sqlBuilder.append(" cr.warehouse_id, ");
		sqlBuilder.append(" cr.rfid_code, ");
		sqlBuilder.append(" cr.f_stock_count, ");
		sqlBuilder.append(" cr.f_move_count, ");
		sqlBuilder.append(" cr.modify_time, ");
		sqlBuilder.append(" cr.rfid_status, ");
		sqlBuilder.append(" cr.batchno, ");
		sqlBuilder.append(" cr.batch_no, ");
		sqlBuilder.append(" cr.remark, ");
		sqlBuilder.append(" cr.invalidDate as invalidDate, ");
		sqlBuilder.append(" cr.productDate as productDate ");
		sqlBuilder.append(" from dxq_coderfid as cr ");
		sqlBuilder.append(" left join dxqyy_product p on p.id = cr.product_id ");
		sqlBuilder.append(" left join dxq_location ttly7u on cr.location_id = ttly7u.id ");
		sqlBuilder.append(" left join dxqyy_productskutemp pst on cr.product_sku_id = pst.id ");
		sqlBuilder.append(" left join dxqyy_unit u on p.unit = u.id ");
		sqlBuilder.append(" left join dxqyy_unit u2 on p.stockUnit = u2.id ");
		sqlBuilder.append(" where 1=1 ");
		if (rfidCode != null && !rfidCode.equals("")) {
			sqlBuilder.append(" and cr.rfid_code = ? ");
			parameter.addParam(rfidCode);
		}
		if (productName != null && !productName.equals("")) {
			sqlBuilder.append(" and p.productName like concat('%',?,'%') ");
			parameter.addParam(productName);
		}
		if (CollectionUtil.isNotEmpty(warehouseIds)) {
			sqlBuilder.append(" and cr.warehouse_id in ");
			sqlBuilder.append(" ( ");
			String separator0 = " , ";
			for (int index = 0; index < warehouseIds.size(); index++) {
				String item = warehouseIds.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(item);
				if (index < warehouseIds.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		sqlBuilder.append(" and cr.ytenant_id=? ");
		parameter.addParam(tenant_id);
		sqlBuilder.append(" and cr.dr = 0 ");
		sqlBuilder.append(" group by cr.id ");
		sqlBuilder.append(" order by cr.id ");
		sqlBuilder.append(" limit ? ,? ");
		parameter.addParam(page);
		parameter.addParam(pageSize);

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new productInfo());
        return (List<productInfo>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public int selectByRfidCodeAndPageTotal(List<String> warehouseIds, String rfidCode, String productName, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select count(*) from ( ");
		sqlBuilder.append(" select cr.id, ");
		sqlBuilder.append(" cr.product_id, ");
		sqlBuilder.append(" p.productName as product_name, ");
		sqlBuilder.append(" cr.product_sku_id, ");
		sqlBuilder.append(" pst.skuName as product_sku_name, ");
		sqlBuilder.append(" u.unitName as unit_name, ");
		sqlBuilder.append(" u2.unitName as stock_unit_name, ");
		sqlBuilder.append(" cr.location_id, ");
		sqlBuilder.append(" ttly7u.rfid_code as locationRfidCode, ");
		sqlBuilder.append(" ttly7u.locationCode as locationCode, ");
		sqlBuilder.append(" ttly7u.locationName as locationName, ");
		sqlBuilder.append(" pst.code as product_sku_code, ");
		sqlBuilder.append(" p.code as product_code, ");
		sqlBuilder.append(" cr.warehouse_id, ");
		sqlBuilder.append(" cr.rfid_code, ");
		sqlBuilder.append(" cr.f_stock_count, ");
		sqlBuilder.append(" cr.f_move_count, ");
		sqlBuilder.append(" cr.modify_time, ");
		sqlBuilder.append(" cr.rfid_status, ");
		sqlBuilder.append(" cr.batchno, ");
		sqlBuilder.append(" cr.batch_no, ");
		sqlBuilder.append(" cr.invalidDate as invalidDate, ");
		sqlBuilder.append(" cr.productDate as productDate ");
		sqlBuilder.append(" from dxq_coderfid as cr ");
		sqlBuilder.append(" left join dxqyy_product p on p.id = cr.product_id ");
		sqlBuilder.append(" left join dxq_location ttly7u on cr.location_id = ttly7u.id ");
		sqlBuilder.append(" left join dxqyy_productskutemp pst on cr.product_sku_id = pst.id ");
		sqlBuilder.append(" left join dxqyy_unit u on p.unit = u.id ");
		sqlBuilder.append(" left join dxqyy_unit u2 on p.stockUnit = u2.id ");
		sqlBuilder.append(" where 1=1 ");
		if (rfidCode != null && !rfidCode.equals("")) {
			sqlBuilder.append(" and cr.rfid_code = ? ");
			parameter.addParam(rfidCode);
		}
		if (CollectionUtil.isNotEmpty(warehouseIds)) {
			sqlBuilder.append(" and cr.warehouse_id in ");
			sqlBuilder.append(" ( ");
			String separator0 = " , ";
			for (int index = 0; index < warehouseIds.size(); index++) {
				String item = warehouseIds.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(item);
				if (index < warehouseIds.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		if (productName != null && !productName.equals("")) {
			sqlBuilder.append(" and p.productName like concat('%',?,'%') ");
			parameter.addParam(productName);
		}
		sqlBuilder.append(" and cr.ytenant_id=? ");
		parameter.addParam(tenant_id);
		sqlBuilder.append(" and cr.dr = 0 ");
		sqlBuilder.append(" group by cr.id ");
		sqlBuilder.append(" order by cr.id) as a ; ");

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new ColumnProcessor(Integer.class);
        return (int) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }


    public List<productInfo> selectCoderfidBylocaid(List<String> locationid, String tenant_id) throws Exception {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select c.*, ttlyu.locationName, ttlyu.locationCode from dxq_coderfid c left join dxq_location ttlyu on c.location_id = ttlyu.id where c.dr = 0 and c.rfid_status IN("+DxqConfig.PdStatic+") ");
		if (CollectionUtil.isNotEmpty(locationid)) {
			sqlBuilder.append(" and c.location_id in ( ");
			String separator0 = " , ";
			for (int index = 0; index < locationid.size(); index++) {
				String item = locationid.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(item);
				if (index < locationid.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		sqlBuilder.append(" and c.ytenant_id=?; ");
		parameter.addParam(tenant_id);

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new productInfo());
        return (List<productInfo>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public List<storeRecord> selectstoreRecordByRfid(List<String> ids, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select * from dxq_storerecord where dr=0 AND ytenant_id=? ");
		parameter.addParam(tenant_id);
		if (CollectionUtil.isNotEmpty(ids)) {
			sqlBuilder.append(" AND rfidcodeId in ( ");
			String separator0 = " , ";
			for (int index = 0; index < ids.size(); index++) {
				String item = ids.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(item);
				if (index < ids.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		sqlBuilder.append(" order by id DESC; ");

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new AutoBeanListProcessor(new storeRecord());
        return (List<storeRecord>) ymsJdbcApi.queryForList(sql, parameter, processor);
    }


    public int selectByRfidCodeAndPageHuoWeiTotal(List<String> rfidCode, String productName, String tenant_id) {
        SQLParameter parameter = new SQLParameter(true);
        StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append(" select count(*) from ( ");
		sqlBuilder.append(" select cr.id, ");
		sqlBuilder.append(" cr.product_id, ");
		sqlBuilder.append(" p.productName as product_name, ");
		sqlBuilder.append(" cr.product_sku_id, ");
		sqlBuilder.append(" pst.skuName as product_sku_name, ");
		sqlBuilder.append(" u.unitName as unit_name, ");
		sqlBuilder.append(" u2.unitName as stock_unit_name, ");
		sqlBuilder.append(" cr.location_id, ");
		sqlBuilder.append(" ttly7u.rfid_code as locationRfidCode, ");
		sqlBuilder.append(" ttly7u.locationCode as locationCode, ");
		sqlBuilder.append(" ttly7u.locationName as locationName, ");
		sqlBuilder.append(" pst.code as product_sku_code, ");
		sqlBuilder.append(" p.code as product_code, ");
		sqlBuilder.append(" cr.warehouse_id, ");
		sqlBuilder.append(" cr.rfid_code, ");
		sqlBuilder.append(" cr.f_stock_count, ");
		sqlBuilder.append(" cr.f_move_count, ");
		sqlBuilder.append(" cr.modify_time, ");
		sqlBuilder.append(" cr.rfid_status, ");
		sqlBuilder.append(" cr.batchno, ");
		sqlBuilder.append(" cr.batch_no, ");
		sqlBuilder.append(" cr.invalidDate as invalidDate, ");
		sqlBuilder.append(" cr.productDate as productDate ");
		sqlBuilder.append("  ");
		sqlBuilder.append(" from dxq_location as dl ");
		sqlBuilder.append(" inner join dxq_coderfid as cr on cr.location_id = dl.id ");
		sqlBuilder.append(" left join dxqyy_product p on p.id = cr.product_id ");
		sqlBuilder.append(" left join dxq_location ttly7u on cr.location_id = ttly7u.id ");
		sqlBuilder.append(" left join dxqyy_productskutemp pst on cr.product_sku_id = pst.id ");
		sqlBuilder.append(" left join dxqyy_unit u on p.unit = u.id ");
		sqlBuilder.append(" left join dxqyy_unit u2 on p.stockUnit = u2.id ");
		sqlBuilder.append(" where 1=1 ");
		if (CollectionUtil.isNotEmpty(rfidCode)) {
			sqlBuilder.append(" and dl.rfid_code in ");
			sqlBuilder.append(" ( ");
			String separator0 = " , ";
			for (int index = 0; index < rfidCode.size(); index++) {
				String rfid = rfidCode.get(index);
				sqlBuilder.append(" ? ");
				parameter.addParam(rfid);
				if (index < rfidCode.size() - 1) {
					sqlBuilder.append(separator0);
				}
			}
			sqlBuilder.append(" ) ");
		}
		if (productName != null && !productName.equals("")) {
			sqlBuilder.append(" and p.productName like concat('%',?,'%') ");
			parameter.addParam(productName);
		}
		sqlBuilder.append(" and cr.ytenant_id=? ");
		parameter.addParam(tenant_id);
		sqlBuilder.append(" and dl.dr = 0 ");
		sqlBuilder.append(" and cr.dr = 0 ");
		sqlBuilder.append(" group by cr.id ");
		sqlBuilder.append(" order by cr.id) as a ; ");

        
        String sql = sqlBuilder.toString();
        ResultSetProcessor processor = new ColumnProcessor(Integer.class);
        return (int) ymsJdbcApi.queryForObject(sql, parameter, processor);
    }

}
